package org.shock.ss.sms.commons.excel.export.impl;

import java.io.FileOutputStream;
import java.util.ArrayList;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddressList;
import org.shock.ss.sms.commons.db.DbTools;
import org.shock.ss.sms.commons.excel.export.BaseExcelExport;

public class BaseExcelExportImpl implements BaseExcelExport {
	public String sheetname = "";
	public String filename = "";
	public HSSFWorkbook _wb = null;
	public HSSFSheet st = null;
	public int sheetcount = 0;
	public int headlines = 1;
	public int nextline = 0;
	public String excelpath = "";
	public String excelname = "";
	public List dateList;// 需要显示的数据列表

	/** 打印调试语句 */
	public void out(Object object) {
		// System.out.println(object);
	}

	public BaseExcelExportImpl() {

		_wb = new HSSFWorkbook();
		out("构造");
	}

	public List<List> changeSqlToList(String sql) throws Exception {
		return DbTools.getList(sql);
	}

	public HSSFSheet creationSheet() throws Exception {
		return _wb.createSheet(sheetname);
	}

	public HSSFSheet setBody(List<List> list) throws Exception {
		int lines = headlines;
		for (int i = 1; i < list.size(); i++) {
			HSSFRow row = st.createRow(lines++);
			List headList = (List) list.get(i);
			for (int j = 0; j < headList.size(); j++) {
				HSSFCell cell = row.createCell((short) (j));
				// cell.setEncoding(cell.ENCODING_UTF_16);//3.6版本不支持了故注释掉
				// 3.2版本一下需要设置
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				// cell.setCellValue("".equals(headList.get(j).toString())?"":headList.get(j).toString());
				cell.setCellValue(headList.get(j).toString());
			}
		}
		out("表体");
		return st;

	}

	public HSSFCellStyle setHeadCss() {
		HSSFFont font = _wb.createFont();
		HSSFCellStyle cellStyle = _wb.createCellStyle();
		font.setColor(HSSFFont.COLOR_RED);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);//
		cellStyle.setFont(font);
		return cellStyle;
	}

	public String setDataListGetExcel(List<List> list, String filename) throws Exception {
		writeExcel(list, filename);
		out("调用");
		getExcel();
		return getExcelPath();
	}

	public void writeExcel(List<List> list, String filename) throws Exception {
		if ("".equals(sheetname)) {
			this.sheetname = filename;
		}
		dateList = list;
		this.filename = filename;
		st = creationSheet();
		st = setHead(list);
		st = setBody(list);
		st = setEnd(list);
		addValidationDataList();

		out("生成poi文件");
	}

	public String setDataListGetExcel(List<List>[] list, String filename, String[] sheetname) throws Exception {
		for (int i = 0; i < list.length; i++) {
			if (i <= sheetname.length & sheetname[i] != null) {
				this.sheetname = sheetname[i];
			} else {
				this.sheetname = "sheet" + String.valueOf(i);
			}
			sheetcount = list.length;
			writeExcel(list[i], filename);

		}
		getExcel();
		return getExcelPath();
	}

	public HSSFSheet setEnd(List<List> list) throws Exception {
		// TODO Auto-generated method stub
		return st;
	}

	public String getExcelPath() throws Exception {
		// TODO Auto-generated method stub
		return this.excelpath;
	}

	public HSSFSheet setHead(List<List> list) throws Exception {
		HSSFRow row = st.createRow(0);
		HSSFCellStyle cellStyle = setHeadCss();
		if (list.size() > 0) {
			List headList = (List) list.get(0);
			for (int j = 0; j < headList.size(); j++) {
				HSSFCell cell = row.createCell((short) (j));
				// cell.setEncoding(cell.ENCODING_UTF_16);//3.6版本不支持了故注释掉
				// 3.2版本一下需要设置
				cell.setCellStyle(cellStyle);
				cell.setCellType(HSSFCell.CELL_TYPE_STRING);
				cell.setCellValue(headList.get(j).toString());
			}
		}
		out("表头");
		return st;
	}

	public String setSqlGetExcel(String sql, String filename) throws Exception {
		List list = changeSqlToList(sql);
		out(list);
		setDataListGetExcel(list, filename);
		return getExcelPath();
	}

	public String setSqlGetExcel(String[] sql, String filename, String[] sheetname) throws Exception {
		for (int i = 0; i < sql.length; i++) {
			if (i <= sheetname.length & sheetname[i] != null) {
				this.sheetname = sheetname[i];
			} else {
				this.sheetname = "sheet" + String.valueOf(i);
			}
			sheetcount = sql.length;
			setSqlGetExcel(sql[i], filename);
		}
		getExcel();
		return getExcelPath();
	}

	public void getExcel() throws Exception {
		out(filename);
		String path = "";
		path = this.getClass().getResource("/").getPath();
		path = path.substring(1, path.lastIndexOf("W") - 1);
		path = path + "/upload";
		path = path.replace("%20", " ");
		FileOutputStream fileOut = new FileOutputStream(path + "/" + "" + filename + ".xls");
		_wb.write(fileOut);// 把Workbook对象输出到文件workbook.xls中
		fileOut.close();
		this.excelpath = path + "/" + "" + filename + ".xls";
		this.excelname = filename + ".xls";
		System.out.println(this.excelpath);
	}

	/** 添加导出模板的单元格限制条件 */
	public void addValidationDataList() throws Exception {
		/*
		 * String stringArray[]={"基尔加丹","伊利丹","蛋的蛋","阿克蒙德"}; DVConstraint
		 * constraint = DVConstraint.createExplicitListConstraint(stringArray); //
		 * 设置数据有效性加载在哪个单元格上。 // 四个参数分别是：起始行、终止行、起始列、终止列 CellRangeAddressList
		 * regions = new CellRangeAddressList(1, 100, 3, 3);
		 *  // 数据有效性对象 HSSFDataValidation data_validation = new
		 * HSSFDataValidation(regions, constraint); //
		 * data_validation.createErrorBox("Invalidate!", "please input again!");
		 * System.out.println(regions); System.out.println(constraint);
		 * System.out.println(data_validation); System.out.println(st);
		 * st.addValidationData(data_validation);
		 */

	}

	public static void main(String[] args) {
		BaseExcelExportImpl export = new BaseExcelExportImpl();
		List[] list1 = new List[3];
		String[] a = { "测试", "是", "啊", "单元格限制测试" };
		for (int j = 0; j < 3; j++) {
			List list = new ArrayList();
			for (int i = 0; i < 100; i++) {
				List list_ = new ArrayList();
				list_.add("语文" + i + a[j]);
				list_.add("B" + i + a[j]);
				list_.add("C" + i + a[j]);
				list_.add("");
				list.add(list_);
			}
			list1[j] = list;
		}
		try {

			export.setDataListGetExcel(list1, "导出测试", a);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public String getExcelName() throws Exception {
		// TODO Auto-generated method stub
		return this.excelname;
	}

}
